package light.head.dao.el;

import java.math.BigInteger;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import light.head.constant.Parm;
import light.head.dto.StudentSign;
import light.head.enm.RESULT;
import light.head.framework.base.BaseDAO;
import light.head.framework.entity.PageBean;
import light.head.model.el.UserClassesRelation;
import light.head.model.el.UserClassesRelationPK;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Service;


/**
 * 班级学生关系表
 */
@Service
public class UserClassesRelationDAO extends BaseDAO<UserClassesRelation, UserClassesRelationPK>{
	
	private final String hql_findByClcCode = "FROM UserClassesRelation ucr WHERE ucr.clc = ?";
	private final String findByClcCodeAndStatus3 = "FROM UserClassesRelation ucr WHERE ucr.clc = ? AND ucr.status = 3";
	/**
	 * 根据班级code 查询信息
	 * @return
	 */
	public List<UserClassesRelation> findByClcCode(Integer code){
		if(code == null){
			 return null;
		}
		return find(hql_findByClcCode,code);
	}
	
	public List<UserClassesRelation> findByClcCodeAndStatus3(Integer code){
		if(code == null){
			 return null;
		}
		return find(findByClcCodeAndStatus3,code);
	}
	
	public UserClassesRelation getUcrByClcAndUc(Integer clc,Integer uc){
		String hql = "FROM UserClassesRelation ucr WHERE ucr.clc = ? AND ucr.uc = ?";
		return (UserClassesRelation)get(hql,clc,uc);
	} 
	
	
	public RESULT findByMap(Integer oc , Map<String, String> map, PageBean pb){
		if(oc == null){
			return null;
		}
		StringBuffer hql = new StringBuffer("FROM UserClassesRelation ucr WHERE ucr.clc = ? ");
		List<Object> list = new ArrayList<Object>();
		if(map != null){
			String clc = map.get(Parm.CODE);
			String name = map.get(Parm.NAME);
			String status = map.get(Parm.STATUS);
			if(isNotNull(clc)){
				list.add(Integer.parseInt(clc));
			}
			if(isNotNull(name)){
				hql.append(" AND ucr.uc IN (SELECT ui.code FROM UserInfo ui WHERE ui.realName LIKE ? AND ui.oc = ?)");
				list.add("%" + name + "%");
				list.add(oc);
			}
			if(isNotNull(status)){
				hql.append(" AND ucr.status = ?");
				list.add(Byte.parseByte(status));
			}
			
		}
		hql.append(" ORDER BY ucr.uc ");
		return findToPageBean(hql.toString(), pb, list);
	}
	//阅卷管理时查询
	public RESULT findByUc(Integer oc , Map<String, String> map, PageBean pb){
		if(oc == null){
			return null;
		}
		StringBuffer hql = new StringBuffer("FROM UserClassesRelation ucr WHERE ucr.oc = ? ");
		List<Object> list = new ArrayList<Object>();
		list.add(oc);
		if(map != null){
			String clc = map.get(Parm.CODE);
			String name = map.get(Parm.NAME);
			String status = map.get(Parm.STATUS);
			if(isNotNull(clc)){
				list.add(Integer.parseInt(clc));
			}
			if(isNotNull(name)){
				hql.append(" AND ucr.uc IN (SELECT ui.code FROM UserInfo ui WHERE ui.realName LIKE ?)");
				list.add("%" + name + "%");
				list.add(oc);
			}
			if(isNotNull(status)){
				hql.append(" AND ucr.status = ?");
				list.add(Byte.parseByte(status));
			}
			
		}
		hql.append(" ORDER BY ucr.uc ");
		return findToPageBean(hql.toString(), pb, list);
	}
	
	public RESULT updateAllSign(Integer clc){
		String sql = "UPDATE UserClassesRelation ucr SET ucr.status = 3 WHERE ucr.clc = ?";
		Integer count = executeSql(sql, clc);
		if(count >= 0){
			return RESULT.SUCCESS;
		} else {
			return RESULT.ERROR;
		}
	} 
	
	
	/**
	 * 学生报名查询
	 */
	public RESULT queryStudentSignInfoByMap(List<Integer> ocs,Integer uc,Map<String, String> map, final PageBean pageBean){
		if(ocs == null || uc == null){
			return null;
		}
		StringBuffer sb = new StringBuffer();
		List<Object> parm = new ArrayList<Object>();
		sb.append("SELECT S.code ,S.cName, S.startSignTime, S.endSignTime, S.beginTime, S.finishTime ,S.num, S.how, S.ocn , S.numed ,S.isSign, S.status FROM ");
		sb.append("(SELECT c.code AS code, c.name AS cName, c.startSignTime AS startSignTime, c.endSignTime AS endSignTime, c.beginTime AS beginTime, c.finishTime AS finishTime, c.num AS num, c.how AS how , o.name AS ocn , count(ucr.uc) AS numed, ");
		sb.append("(SELECT ucr1.signTime FROM  UserClassesRelation ucr1 WHERE ucr1.clc = c.code AND ucr1.uc = ? ) AS isSign, ");
		sb.append("(SELECT ucr2.status FROM  UserClassesRelation ucr2 WHERE ucr2.clc = c.code AND ucr2.uc = ? ) AS status ");
		parm.add(uc);
		parm.add(uc);
		sb.append("FROM Classes c ");
		sb.append("JOIN Organ o ON o.code = c.oc ");
		sb.append("LEFT JOIN UserClassesRelation ucr ON ucr.clc = c.code AND ucr.status <> 2 ");
		sb.append("WHERE c.deleted = 0 AND o.deleted = 0 AND c.oc IN( ");
		for (Integer i : ocs) {
			sb.append("?,");
			parm.add(i);
		}
		sb.deleteCharAt(sb.length() - 1).append(")");
		sb.append("GROUP BY c.code DESC) S WHERE 1 = 1 ");
		if(map != null){
			String name = map.get(Parm.NAME);
			String type = map.get(Parm.TYPE);
			String startTime = map.get(Parm.TIME1);
			String endTime = map.get(Parm.TIME2);
			String beginTime = map.get(Parm.TIME3);
			String finishTime = map.get(Parm.TIME4);
			if(isNotNull(name)){
				sb.append(" AND S.cName LIKE ? ");
				parm.add("%" + name + "%");
			}
			if(isNotNull(type)){
				if(Byte.parseByte(type) == 1){
					sb.append(" AND S.isSign IS NULL AND S.startSignTime <= NOW() AND  NOW() <= S.endSignTime AND S.how <> 3");
				}else if(Byte.parseByte(type) == 2){
					sb.append(" AND S.isSign IS NOT NULL ");
				}
			}
			if(isNotNull(startTime) && isNotNull(endTime)){
				sb.append(" AND S.endSignTime BETWEEN str_to_date(?,'%Y-%m-%d') AND str_to_date(?,'%Y-%m-%d %H:%i:%s') ");
				parm.add(startTime);
				parm.add(endTime + " 23:59:59");
			}else if(isNotNull(startTime) && !isNotNull(endTime)){
				sb.append(" AND S.endSignTime >= str_to_date(?,'%Y-%m-%d') ");
				parm.add(startTime);
			}else if(!isNotNull(startTime) && isNotNull(endTime)){
				sb.append(" AND S.endSignTime <= str_to_date(?,'%Y-%m-%d') ");
				parm.add(endTime);
			}
			
			if(isNotNull(beginTime) && isNotNull(finishTime)){
				sb.append(" AND S.beginTime BETWEEN str_to_date(?,'%Y-%m-%d') AND str_to_date(?,'%Y-%m-%d %H:%i:%s') ");
				parm.add(beginTime);
				parm.add(finishTime + " 23:59:59");
			}else if(isNotNull(beginTime) && !isNotNull(finishTime)){
				sb.append(" AND S.beginTime >= str_to_date(?,'%Y-%m-%d') ");
				parm.add(beginTime);
			}else if(!isNotNull(beginTime) && isNotNull(finishTime)){
				sb.append(" AND S.beginTime <= str_to_date(?,'%Y-%m-%d') ");
				parm.add(finishTime);
			}
		}
		
		final String sql = sb.toString();
		final String countSql = sb.replace(7, sb.indexOf("FROM"), "COUNT(*) ").toString();
		final List<Object> parmList = parm;
		return this.getHibernateTemplate().execute(new HibernateCallback<RESULT>() {

			@SuppressWarnings("unchecked")
			@Override
			public RESULT doInHibernate(Session session) throws HibernateException, SQLException {
				SQLQuery query = session.createSQLQuery(sql);
				for (int i = 0; i < parmList.size(); i++) {
					query.setParameter(i, parmList.get(i));
				}
				query.setFirstResult((pageBean.getPage() - 1) * pageBean.getPageSize());
				query.setMaxResults(pageBean.getPageSize());
				
				List<Object[]> resultSet = query.list();
				if (resultSet != null) {
					List<StudentSign> ssList = new ArrayList<StudentSign>();
					StudentSign ss = null;
					Date time = new Date();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					String today = sdf.format(time);
					try {
						time = sdf.parse(today);
					} catch (ParseException e) {
						e.printStackTrace();
					}
					for (Object[] row : resultSet) {
						ss = new StudentSign();
						Integer code = row[0] != null ? (Integer)row[0] :null;
						String name = row[1] != null ? String.valueOf(row[1]) : null;
						Date startSignTime = row[2] != null ? (Date)row[2] : null;
						Date endSignTime = row[3] != null ? (Date)row[3] : null;
						Date beginTime = row[4] != null ? (Date)row[4] : null;
						Date finishTime = row[5] != null ? (Date)row[5] : null;
						Short num = row[6] != null ? (Short)row[6] : 0;
						Byte how = row[7] != null ? ((Byte)row[7]).byteValue() : null;
						String ocn = row[8] != null ? String.valueOf(row[8]) : null;
						Short numed = row[9] != null ? ((BigInteger)row[9]).shortValue() :0;
						Date isSign = row[10] != null ? (Date)row[10] : null;
						Byte status = row[11] != null ? ((Integer)row[11]).byteValue() :null;
						ss.setCode(code);
						ss.setName(name);
						ss.setStartSignTime(startSignTime);
						ss.setEndSignTime(endSignTime);
						ss.setBeginTime(beginTime);
						ss.setFinishTime(finishTime);
						ss.setNum(num);
						ss.setNumed(numed);
						ss.setHow(how);
						ss.setOcn(ocn);
						ss.setStatus(status);
						if(isSign == null){
							ss.setIsSign(false);
						}else{
							ss.setIsSign(true);
						}
						if(num != 0 && numed >= num){
							ss.setAbleSign(false);
						}else if(num == 0){
							if(ss.getStartSignTime() != null && ss.getEndSignTime() != null){
								if(ss.getStartSignTime().getTime() - time.getTime() <= 0 && ss.getEndSignTime().getTime() - time.getTime() >= 0){
									ss.setAbleSign(true);
								}else{
									ss.setAbleSign(false);
								}
							}else if(ss.getStartSignTime() != null && ss.getEndSignTime() == null){
								if(ss.getStartSignTime().getTime() - time.getTime() <= 0){
									ss.setAbleSign(true);
								}else{
									ss.setAbleSign(false);
								}
							}else if(ss.getStartSignTime() == null && ss.getEndSignTime() != null){
								if(ss.getEndSignTime().getTime() - time.getTime() >= 0){
									ss.setAbleSign(true);
								}else{
									ss.setAbleSign(false);
								}
							}else{
								ss.setAbleSign(true);
							}
						}else if(num != 0 && numed < num){
							if(ss.getStartSignTime() != null && ss.getEndSignTime() != null){
								if(ss.getStartSignTime().getTime() - time.getTime() <= 0 && ss.getEndSignTime().getTime() - time.getTime() >= 0){
									ss.setAbleSign(true);
								}else{
									ss.setAbleSign(false);
								}
							}else if(ss.getStartSignTime() != null && ss.getEndSignTime() == null){
								if(ss.getStartSignTime().getTime() - time.getTime() <= 0){
									ss.setAbleSign(true);
								}else{
									ss.setAbleSign(false);
								}
							}else if(ss.getStartSignTime() == null && ss.getEndSignTime() != null){
								if(ss.getEndSignTime().getTime() - time.getTime() >= 0){
									ss.setAbleSign(true);
								}else{
									ss.setAbleSign(false);
								}
							}else{
								ss.setAbleSign(true);
							}
						}
						ssList.add(ss);
					}
					pageBean.setResultList(ssList);
					SQLQuery countQuery = session.createSQLQuery(countSql);
					for (int i = 0; i < parmList.size(); i++) {
						countQuery.setParameter(i, parmList.get(i));
					}
					
					Long total = ((BigInteger) countQuery.uniqueResult()).longValue();
					pageBean.setTotal(total);
					pageBean.compute();
					return RESULT.SUCCESS;
				}
				return RESULT.ERROR;
			}
		});
	}
	
	/**
	 * 查询学生信息
	 */
	public List<UserClassesRelation> findByClcAndName(Integer clc,String name,Integer oc){
		String hql = null;
		if(isNotNull(name)){
			hql = "FROM UserClassesRelation ucr WHERE ucr.clc = ? AND ucr.uc IN (SELECT ui.code FROM UserInfo ui WHERE ui.deleted = 0 AND ui.realName LIKE ? AND ui.oc = ?)";
			return findI(hql,clc, "%" + name + "%",oc);
		} else {
			hql = "FROM UserClassesRelation ucr WHERE ucr.clc = ? ";
			return findI(hql,clc);
		}
	}
}
